package dao.impl;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import model.Datentraeger;
import model.ModelBase;
import dao.DAO;
import dao.DatentraegerSqlDAO;
import dao.util.DAOException;
import dao.util.DaoAnnotation;
import dao.util.DaoContextEnum;
import dao.util.JDBCDateHelper;


@DaoAnnotation(className="DatentraegerSqlDAO", use = DaoContextEnum.MYSQL, daoInterface = DAO.class)
public class DatentraegerSqlDaoMySqlImpl implements DatentraegerSqlDAO
{
	private static final String SQL_INSERT = 			"INSERT INTO datentraeger(D_Name, D_Datum, D_TimeStamp) "	+ 
														"VALUES (?, ?, ?)";
	private static final String SQL_UPDATE = 			"UPDATE datentraeger " + 
														"SET D_TimeStamp = ?, " + 
														"    D_Name = ? " + 
														"WHERE D_id = ? "	+ 
														"  AND D_TimeStamp = ? ";
	private static final String SQL_DELETE = 			"DELETE FROM datentraeger " + 
														"WHERE D_id = ? " + 
														"  AND D_TimeStamp = ?";
	private static final String SQL_DELETE_ALL = 		"DELETE FROM datentraeger  ";
	private static final String SQL_SELECT_BY_ID = 		"SELECT D_id, D_Name, D_Datum, D_TimeStamp " + 
														"FROM datentraeger " + 
														"WHERE D_id = ?";
	private static final String SQL_SELECT_BY_NAMPT = 	"SELECT D_id, D_Name, D_Datum, D_TimeStamp " + 
														"FROM datentraeger " + 
														"WHERE D_Name like ?";
	private static final String SQL_SELECT_ALL = 		"SELECT D_id, D_Name, D_Datum, D_TimeStamp " + 
														"FROM datentraeger";

	private PreparedStatement insertStmt 				= null;
	private PreparedStatement updateStmt 				= null;
	private PreparedStatement deleteStmt				= null;
	private PreparedStatement deleteAllStmt 			= null;
	private PreparedStatement selectByIdStmt 			= null;
	private PreparedStatement selectByNamPtStmt 		= null;
	private PreparedStatement selectAllStmt 			= null;

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.DatentraegerSQLDAO#delete(java.sql.Connection, model.ModelBase)
	 */
	@Override
	public int delete(Connection connection, ModelBase datentraeger)
	{
		if(connection == null)
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if (datentraeger == null)
		{
			throw new IllegalArgumentException("Datentraeger must not be null!");
		}
		if (datentraeger.getId() == null || datentraeger.getLastModifiedTimeStamp() == null)
		{
			throw new IllegalArgumentException("Datentraeger has to have ID and TIMESTAMP");
		}
		int recordsCleared = 0;
		try	
		{
			if (deleteStmt == null)	
			{
				deleteStmt = connection.prepareStatement(SQL_DELETE);
			}

			deleteStmt.setLong(			1, datentraeger.getId());
			deleteStmt.setTimestamp(	2, JDBCDateHelper.toSqlTimestamp(datentraeger.getLastModifiedTimeStamp()));
			recordsCleared = 			deleteStmt.executeUpdate();

		} 
		catch (SQLException e)
		{
			throw new DAOException("Datentraeger could not be cleared!", e);
		}
		return recordsCleared;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.DatentraegerSQLDAO#deleteFromShop(java.sql.Connection,
	 * model.ModelBase)
	 */
	@Override
	public int deleteAllDatentraeger(Connection connection)
	{
		if(connection == null)
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		int recordsCleared = 0;
		try	
		{
			if (deleteAllStmt == null)	
			{
				deleteAllStmt = 	connection.prepareStatement(SQL_DELETE_ALL);
			}
			recordsCleared = 		deleteAllStmt.executeUpdate();

		} 
		catch (SQLException e)
		{
			throw new DAOException("Datentraeger could not be cleared!", e);
		}
		return recordsCleared;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.DatentraegerSQLDAO#findAll(java.sql.Connection)
	 */
	@Override
	public List<Datentraeger> findAll(Connection connection)	
	{
		if(connection == null)
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		List<Datentraeger> result = 	new ArrayList<Datentraeger>();
		Datentraeger datentraeger;
		try	
		{
			if (selectAllStmt == null)
			{
				selectAllStmt = 	connection.prepareStatement(SQL_SELECT_ALL);
			}
			ResultSet rs = 			selectAllStmt.executeQuery();

			while (rs.next())
			{
				datentraeger = 		new Datentraeger(rs.getLong(D_ID), rs.getString(D_NAME), rs.getDate(D_DATUM));
				datentraeger.setLastModifiedTimeStamp(JDBCDateHelper.toDate(rs.getTimestamp(D_TIMESTAMP)));
				result.add(datentraeger);
			}
			rs.close();
		} 
		catch (SQLException e)
		{
			throw new DAOException("Error at finding all from Datentraeger!", e);
		}
		return result;
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.DatentraegerSQLDAO#findById(java.sql.Connection, java.lang.Long)
	 */
	public Datentraeger findById(Connection con, Long id)	
	{
		if(con == null)
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if(id == null)
		{
			throw new IllegalArgumentException("ID must not be null!");
		}
		Datentraeger foundDatentraeger = 		null;
		try	{
			if (selectByIdStmt == null)	{
				selectByIdStmt = 		con.prepareStatement(SQL_SELECT_BY_ID);
			}

			selectByIdStmt.setLong(		1, id);
			ResultSet rs = 				selectByIdStmt.executeQuery();

			while (rs.next()) {
				foundDatentraeger = 	new Datentraeger(	id, 
															rs.getString(D_NAME), 
															rs.getDate(D_DATUM));
				
				foundDatentraeger.setLastModifiedTimeStamp(JDBCDateHelper.toDate(
															rs.getTimestamp(D_TIMESTAMP)));
			}
			rs.close();
		} catch (SQLException e){
			throw new DAOException("Datentraeger could not be found!", e);
		}
		return foundDatentraeger;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.DatentraegerSQLDAO#findByNamePart(java.sql.Connection,
	 * java.lang.String)
	 */
	@Override
	public List<Datentraeger> findByNamePart(Connection connection, String part)
	{
		if(connection == null)
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if(part == null)
		{
			throw new IllegalArgumentException("Namepart must not be null!");
		}
		if(part.trim().length()<=2)
		{
			throw new IllegalArgumentException("Namepart has to have more than two chars!");
		}
		List<Datentraeger> result = 			new ArrayList<Datentraeger>();
		Datentraeger datentraeger;
		try	
		{
			if (selectByNamPtStmt == null) 
			{
				selectByNamPtStmt = 		connection.prepareStatement(SQL_SELECT_BY_NAMPT);
			}
			selectByNamPtStmt.setString(	1, part);
			ResultSet rs = 					selectByNamPtStmt.executeQuery();

			while (rs.next())
			{
				datentraeger = 				new Datentraeger(	rs.getLong(D_ID), 
																rs.getString(D_NAME), 
																rs.getDate(D_DATUM));
				datentraeger.setLastModifiedTimeStamp(JDBCDateHelper.toDate(
																rs.getTimestamp(D_TIMESTAMP)));
				result.add(datentraeger);
			}
			rs.close();
		} 
		catch (SQLException e)
		{
			throw new DAOException("Error at finding all Datentraeger with namepart: " + part + "!", e);
		}
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see dao.DatentraegerSQLDAO#save(java.sql.Connection, model.Abteilung)
	 */
	@Override
	public int save(Connection connection, ModelBase data)
	{
		if(connection == null)
		{
			throw new IllegalArgumentException("Connection must not be null!");
		}
		if (data == null)
		{
			throw new IllegalArgumentException("Datentraeger must not be null!");
		}
		int recordsAffected = 0;
		Datentraeger datentraeger = (Datentraeger) data;
		recordsAffected = 			(datentraeger.isNew() ? 
									insert(connection, datentraeger) : 
									update(connection, datentraeger));
		return recordsAffected;
	}

	private int update(Connection connection, Datentraeger datentraeger)	
	{
		if (datentraeger.getId() == null || datentraeger.getLastModifiedTimeStamp() == null)
		{
			throw new IllegalArgumentException("Datentraeger has to have ID and TIMESTAMP");
		}
		int recordsAffected = 0;
		Date now = JDBCDateHelper.killUtilNanos(new Date());
		try	
		{
			if (updateStmt == null)	
			{
				updateStmt = 			connection.prepareStatement(SQL_UPDATE, PreparedStatement.RETURN_GENERATED_KEYS);
			}
			updateStmt.setTimestamp(	1, JDBCDateHelper.toSqlTimestamp(now));
			updateStmt.setString(		2, datentraeger.getName());
			
			updateStmt.setLong(			3, datentraeger.getId());
			updateStmt.setTimestamp(	4, JDBCDateHelper.toSqlTimestamp(datentraeger.getLastModifiedTimeStamp()));
			
			recordsAffected = 			updateStmt.executeUpdate();
			
			datentraeger.setLastModifiedTimeStamp(now);
			
		} 
		catch (SQLException e)
		{
			throw new DAOException("Datentraeger could not be updated!", e);
		}
		return recordsAffected;
	}

	private int insert(Connection connection, Datentraeger datentraeger)
	{
		int recordsAffected = 0;
		Date now = JDBCDateHelper.killUtilNanos(new Date());
		
		try	
		{
			if (insertStmt == null)	
			{
				insertStmt = 			connection.prepareStatement(SQL_INSERT, PreparedStatement.RETURN_GENERATED_KEYS);
			}
			insertStmt.setString(		1, datentraeger.getName());
			insertStmt.setDate(			2, JDBCDateHelper.toSqlDate(datentraeger.getCreationDate()));
			insertStmt.setTimestamp(	3, JDBCDateHelper.toSqlTimestamp(now));
			recordsAffected = 			insertStmt.executeUpdate();
			
			ResultSet generatedKeys = 	insertStmt.getGeneratedKeys();

			if (generatedKeys.next())
			{
				long currentID = 		generatedKeys.getLong(1);
				datentraeger.setId(		currentID);
				
				datentraeger.setLastModifiedTimeStamp(now);
			}
			generatedKeys.close();
		} 
		catch (SQLException e)
		{
			throw new DAOException("Datentraeger could not be inserted!", e);
		}
		return recordsAffected;
	}

	@Override
	public int delete(Connection con, Datentraeger data) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public void deleteAll(Connection con) {
		// TODO Auto-generated method stub
		return;
	}

	@Override
	public Datentraeger findByName(Connection con, String name) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Datentraeger save(Connection con, Datentraeger data) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Datentraeger read(InputStream in) throws IOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void write(OutputStream out, Datentraeger data) throws IOException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Collection<Datentraeger> readMultiple(InputStream in)
			throws IOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void writeMultiple(OutputStream out, Collection<Datentraeger> data)
			throws IOException {
		// TODO Auto-generated method stub
		
	}


}

